﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Globalization;

using CarlosAg.ExcelXmlWriter;
using CarlosAg.ExcelXmlWriter.Schemas;
using System.IO;
using System.Configuration;

public partial class Adm_GenExcel : System.Web.UI.Page
{
		DataTable m_Table = null;
		CultureInfo currentCultureInfo;

		protected void Page_Load(object sender, EventArgs e)
		{
				return;

				currentCultureInfo =
																				CultureInfo.GetCultureInfo(ConfigurationManager.AppSettings.Get("CurrentCulture"));

				if (Session["dataset_2"] != null)
				{
						m_Table = (DataTable)Session["dataset_2"];

						//string nome_report = Session["ReportName"].ToString() !="" ? Session["ReportName"].ToString() : "report";

						string nome_report = Session["ReportName"] == null ? "report" : Session["ReportName"].ToString();

						generaExcel(nome_report, Session["ValFiltro"].ToString(), Session["ValSorted"].ToString());
						//generaExcel(Session["ReportName"].ToString(), Session["ValFiltro"].ToString(), Session["ValSorted"].ToString());

				}
		}//fm


		#region METODI

		private void generaExcel(string pReportName, string pFiltro, string pSorted)
		{
				if (m_Table == null)
				{
						//Response.Redirect("index.html");
						return;
				}
				if (m_Table.Rows.Count < 1)
				{
						Response.ClearHeaders();
						Response.Clear();
						//Response.Redirect("index.html");
						return;
				}

				Workbook wBook = new Workbook();
				Worksheet wSheet = null;
				WorksheetCell wCell = null;

				string fileFullName = string.Empty;
				Guid tempGuid = new Guid();

				Dictionary<string, int> dictLenCols = new Dictionary<string, int>();

				try
				{
						wBook.Properties.Created = DateTime.Now;
						wBook.Properties.Title = "Dynamic Report";
						wBook.Properties.Company = "CA";

						//prelevo il nome da dare al foglio di lavoro
						string wSheetname = string.Empty;
						wSheetname = "Dati";

						//apro il workbook e prelevo il primo wSheet altrimenti lo genero
						wSheet = wBook.Worksheets.Add(wSheetname);

						//stili ammessi :
						/*
							* General, General Number, General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, Short Time, Currency, Euro Currency, 
							* Fixed, Standard, Percent, Scientific, Yes/No, True/False, or On/Off.
							* */
						////STILI globali per la formattazione
						string stylProva = @"dd/mm/yyyy\\ hh\\.mm;@";
						WorksheetStyle stile_HEADER_1 = getExcelStyle("Stile_HEADER_1", 10, false, "Black", "Yellow", StyleHorizontalAlignment.Center, StyleInteriorPattern.Solid, "General");  // stile per formato numerico											
						WorksheetStyle stile_NUMERIC0 = getExcelStyle("Stile_NUMERICO", 10, false, "Black", "White", StyleHorizontalAlignment.Right, StyleInteriorPattern.None, "General Number");  // stile per formato numerico											
						WorksheetStyle stile_DECIMALE = getExcelStyle("Stile_DECIMALE", 10, false, "Black", "White", StyleHorizontalAlignment.Right, StyleInteriorPattern.None, "#,##0.00");
						WorksheetStyle stile_SHORTDATA = getExcelStyle("Stile_SHORTDATA", 10, false, "Black", "White", StyleHorizontalAlignment.Center, StyleInteriorPattern.None, "Short Date");
						WorksheetStyle stile_GENERALDATA = getExcelStyle("Stile_GENERALDATA", 10, false, "Black", "White", StyleHorizontalAlignment.Center, StyleInteriorPattern.None, "General Date");
						WorksheetStyle stile_GENERICO = getExcelStyle("Stile_GENERICO", 10, false, "Black", "White", StyleHorizontalAlignment.Left, StyleInteriorPattern.None, "General");

						//aggiungo stili nel workbook a disposizione d'uso
						wBook.Styles.Add(stile_HEADER_1);
						wBook.Styles.Add(stile_NUMERIC0);
						wBook.Styles.Add(stile_DECIMALE);//2 difre decimali
						wBook.Styles.Add(stile_SHORTDATA);
						wBook.Styles.Add(stile_GENERALDATA);
						wBook.Styles.Add(stile_GENERICO);

						int totRighe = m_Table.Select(pFiltro, pSorted).Length;
						int totColonne = m_Table.Columns.Count;

						////inserimento righe e colonne per il foglio 		
						WorksheetRow wRow = null;
						WorksheetColumn wCol = null;

						for (int i = 0; i < totColonne; i++)
						{
								wCol = new WorksheetColumn { AutoFitWidth = true };
								wSheet.Table.Columns.Add(wCol);
						}

						for (int i = 0; i < totRighe; i++)
								wSheet.Table.Rows.Add();

						int columnCounter = 0;
						//Intestazioni colonne
						foreach (DataColumn currentDataColumn in m_Table.Columns)
						{
								//preleva il tipo dato della colonna
								if (wSheet.Table.Columns.Count < columnCounter)
										wCol = wSheet.Table.Columns.Add();
								else
										wCol = wSheet.Table.Columns[columnCounter];

								dictLenCols.Add(currentDataColumn.ColumnName, 0);

								string formatType = getColumnDataType(currentDataColumn.DataType);

								switch (formatType)
								{
										case "General Number":
												wCol.StyleID = stile_NUMERIC0.ID; break;
										case "Currency":
												wCol.StyleID = stile_DECIMALE.ID; break;//stile_DECIMALE
										case "Short Date":
												wCol.StyleID = stile_SHORTDATA.ID; break;
										case "General Date":
												wCol.StyleID = stile_GENERALDATA.ID; break;
										case "General":
												wCol.StyleID = stile_GENERICO.ID; break;
										default:
												break;
								}

								//aggiunge cella con testo e tipo formattazione
								wSheet.Table.Rows[0].Cells.Add(
																							new WorksheetCell(currentDataColumn.ColumnName, DataType.String, stile_HEADER_1.ID));

								columnCounter++;
						}

						//goto Labelesci;
						//Scrittura dati
						int rowCounter = 1;
						int len = 0;
						foreach (DataRow currentDataRow in m_Table.Select(pFiltro, pSorted))
						{
								if (wSheet.Table.Rows.Count <= rowCounter)
										wRow = wSheet.Table.Rows.Add();
								else
										wRow = wSheet.Table.Rows[rowCounter];

								columnCounter = 0;
								string stile = string.Empty;
								string dati = string.Empty;
								string valore = string.Empty;

								foreach (DataColumn currentDataColumn in m_Table.Columns)
								{
										string formatType = getColumnDataType(currentDataColumn.DataType);

										if (wRow.Cells.Count <= columnCounter)
										{
												wCell = new WorksheetCell();
												wRow.Cells.Add(wCell);
										}
										else
												wCell = wRow.Cells[columnCounter];

										if (currentDataRow[currentDataColumn.ColumnName] == DBNull.Value)
										{
												formatType = "General";
												wCell.Data.Type = DataType.String;
												wCell.Data.Text = "";
										}
										else
										{

												switch (formatType)
												{
														case "General Number":
																wCell.StyleID = "Stile_NUMERICO";
																wCell.Data.Type = DataType.Number;
																wCell.Data.Text = Convert.ToDouble(currentDataRow[currentDataColumn.ColumnName], currentCultureInfo).ToString();
																break;
														case "Currency":
																wCell.StyleID = stile_DECIMALE.ID;
																wCell.Data.Type = DataType.Number;
																valore = Convert.ToDouble(currentDataRow[currentDataColumn.ColumnName], currentCultureInfo).ToString();
																wCell.Data.Text = valore.Replace(',', '.');
																break;
														case "Short Date":
																wCell.StyleID = "Stile_SHORTDATA";
																wCell.Data.Type = DataType.DateTime;
																wCell.Data.Text = Convert.ToDateTime(currentDataRow[currentDataColumn.ColumnName], currentCultureInfo).ToShortDateString();
																break;
														case "General Date":
																wCell.StyleID = "Stile_GENERALDATA";
																wCell.Data.Type = DataType.DateTime;
																wCell.Data.Text = Convert.ToDateTime(currentDataRow[currentDataColumn.ColumnName], currentCultureInfo).ToString("yyyy-MM-dd");//.ToString("yyyy-MM-ddTHH:mm:ss.000");
																break;
														case "General":
																wCell.StyleID = "Stile_GENERICO";
																wCell.Data.Type = DataType.String;
																wCell.Data.Text = currentDataRow[currentDataColumn.ColumnName].ToString();
																break;
														default:
																break;
												}

												len = 0;
												len = GetMaxLength(wSheet.Table.Columns[columnCounter].Width, currentDataRow[currentDataColumn.ColumnName].ToString());

												wSheet.Table.Columns[columnCounter].Width = len;
												dictLenCols[currentDataColumn.ColumnName] = len;
										}

										columnCounter++;
								}
								rowCounter++;
						}

						//wSheet.Options.FitToPage = true;

						double aggiunta = 0.0;
						for (int i = 0; i < wSheet.Table.Columns.Count; i++)
						{
								if (wSheet.Table.Columns[i].StyleID.Equals("Stile_GENERICO"))
								{
										wSheet.Table.Columns[i].Width *= 2;
										aggiunta = wSheet.Table.Columns[i].Width / 3;
										wSheet.Table.Columns[i].Width += (int)Math.Ceiling(aggiunta);
								}
						}

						//Abilitazione in automatico del filtro righe
						wSheet.AutoFilter.Range = "R1C1:R" + totRighe.ToString() + "C" + totColonne.ToString();// range in formato R1C1 

						//Fissaggio intestazioni

						//wSheet.Options.SplitHorizontal = 1;
						wSheet.Table.Rows[1].AutoFitHeight = true;
						wSheet.Options.FreezePanes = true;
						wSheet.Options.TopRowBottomPane = 1;
						//wSheet.Options.ViewableRange = wSheet.AutoFilter.Range;
						//wSheet.Options.TopRowVisible = 0;

						//Invio file
						//Labelesci:												
						string nomeFile = pReportName + ".xls";
						//string salvataggio = @"C:\provaExport.xls";
						//fileFullName = Request.PhysicalApplicationPath + "public\\" + nomeFile;
						////wBook.Save(salvataggio);

						Response.ClearHeaders();
						Response.Clear();
						Response.Cache.SetCacheability(HttpCacheability.Private);
						Response.Cache.SetMaxAge(new TimeSpan(0));
						Response.Cache.SetExpires(new DateTime(0));
						Response.Cache.SetNoServerCaching();
						Response.ContentType = "application/vnd.ms-excel";
						Response.AppendHeader("Content-Disposition", "attachment; filename=" + nomeFile);
						//Response.WriteFile(fileFullName);

						wBook.Save(Response.OutputStream);

						Response.Flush();
						//Response.End();  

						//Cancellazione file temporaneo
						//if (File.Exists(fileFullName)) File.Delete(fileFullName);

				}
				catch (FileNotFoundException fne)
				{
						Session["ErrorMessage"] = "FrmShowReport.BtnExport_Click(). Errore File System: " + fne.Message;
						Response.Redirect("FrmError.aspx");
				}
				catch (Exception ex)
				{
						Session["ErrorMessage"] = "FrmShowReport.BtnExport_Click(). Errore File System: " + ex.Message;
						//Response.Redirect("FrmError.aspx");//NOn agganciare errori a questo livello !!
				}
				finally
				{
						//pulizie se necessita 
				}

		}//fm

		private int GetMaxLength(int oldLength, string str)
		{
				if (str == null) str = "";
				//byte[] bs = System.Text.Encoding.Default.GetBytes(str.Trim());
				int newLength = str.Length;
				if (oldLength > newLength)
						return oldLength;
				else
						return newLength;
		}

		private WorksheetStyle getExcelStyle(string pStyleName, int pFontSize, bool pFontBold, String pFontColor, string pBackColor, StyleHorizontalAlignment pAlignment, StyleInteriorPattern pStyleInterior, string pNumbeFormat)
		{
				WorksheetStyle style1 = new WorksheetStyle(pStyleName);
				style1.Font.FontName = "Tahoma";
				style1.Font.Size = pFontSize;
				style1.Font.Bold = pFontBold;
				style1.Alignment.Horizontal = pAlignment;
				style1.Font.Color = pFontColor;
				style1.Interior.Color = pBackColor;
				style1.Interior.Pattern = pStyleInterior;
				style1.NumberFormat = pNumbeFormat;// pNumbeFormat;
				return style1;
		}//fm

		private string getColumnDataType(Type pTypeCol)
		{
				if (pTypeCol == Type.GetType("System.Boolean") ||
																								pTypeCol == Type.GetType("System.Int16") ||
																				pTypeCol == Type.GetType("System.Int32") ||
																								pTypeCol == Type.GetType("System.Int64") ||
																								pTypeCol == Type.GetType("System.Single") ||
																								pTypeCol == Type.GetType("System.TimeSpan") ||
																								pTypeCol == Type.GetType("System.UInt16") ||
																								pTypeCol == Type.GetType("System.UInt32") ||
																								pTypeCol == Type.GetType("System.UInt64"))
						return "General Number";
				else if (pTypeCol == Type.GetType("System.Decimal") ||
																								pTypeCol == Type.GetType("System.Double"))
						return "Currency";
				else if (pTypeCol == Type.GetType("System.DateTime"))
						return "General Date";
				else
						return "General";

		}//fm


		private DataType TypeConvert(Type type)
		{
				switch (type.Name)
				{
						case "Decimal":
						case "Double":
						case "Single":
								return DataType.Number;
						case "Int16":
						case "Int32":
						case "Int64":
						case "SByte":
						case "UInt16":
						case "UInt32":
						case "UInt64":
								return DataType.Number;
						case "String":
								return DataType.String;
						case "DateTime":
								return DataType.String;
						default:
								return DataType.String;
				}
		}//fm


		#endregion
}//fc


